package connectDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.w3c.dom.views.AbstractView;

import entities.Faqentities;
import entities.Packetentities;

public class FaqDB extends AbstractDB implements IDB<Faqentities, Boolean,String> {

	@Override
	public Boolean add(Faqentities e) {
		String sql = "insert into FAQ values(?,?)";
		Connection con =this.condb.getConnectDB();
		PreparedStatement pst;

		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, e.getTitle());
			pst.setString(2, e.getMessage());
			//pst.setInt(1, e.getPacketid());
			return pst.executeUpdate() > 0;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return false;
	}

	@Override
	public Boolean delete(String k) {
		return null;
	}

	@Override
	public Boolean delete1(int i) {
		String sql = "delete from FAQ where id="+i+"";
		Connection con =this.condb.getConnectDB();
		try {
			Statement st = con.createStatement();
			return st.executeUpdate(sql) >0;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public Boolean update(Faqentities e) {
		String sql = "update FAQ set title=?,message=? where id=?";
		Connection con =this.condb.getConnectDB();
		PreparedStatement pst;

		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, e.getTitle());
			pst.setString(2, e.getMessage());
			pst.setInt(3, e.getId());
			return pst.executeUpdate() > 0;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return false;

	}

	@Override
	public Faqentities find(String k) {
		String sql="select * from FAQ where id=?";
		Connection con = this.condb.getConnectDB();
		PreparedStatement pst;
		Faqentities ld = null;
		try {
			pst= con.prepareStatement(sql);
			pst.setString(1, k);
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				int idd=rs.getInt("id");
				String question=rs.getString("title");
				String answer=rs.getString("message");
				ld=  new Faqentities(idd, question, answer)	;	
			}
			rs.close();
			pst.close();
			con.close();
		} catch (SQLException e) {
		
			e.printStackTrace();
		}

		return ld;
	}

	@Override
	public List<Faqentities> findAll() {

		Connection con = this.condb.getConnectDB();
		String sql= "select * from FAQ";
		Faqentities faq=null;
		List<Faqentities> ls= new ArrayList();
		
		try {
			Statement st= con.createStatement();
			ResultSet rs= st.executeQuery(sql);
			while(rs.next()){
				int id=rs.getInt("id");
				String ask=rs.getString("title");
				String answer=rs.getString("title");
				faq = new Faqentities(id, ask, answer);
				ls.add(faq);
			}
			return ls;		
		    
		} catch (SQLException e) {
		
			e.printStackTrace();
		}		
		return null;
		
	}


@Override
public Faqentities find1(int k) {
	return null;
}

public static void main(String[] args) {
	FaqDB x = new FaqDB();
	Faqentities y = new Faqentities(6,"cau hoi 1", "tra loi cau 1");
	//System.out.println(x.add(y));
	//System.out.println(x.delete1(6));
	//System.out.println(x.update(y));
	System.out.println(x.find("23"));
//	List<Faqentities> listst= new ArrayList();
//	listst= x.findAll();
//          for(Faqentities std : listst){
//		System.out.println(std);
//	}
//	System.out.println(x.findtotalrows());
//	for(Faqentities j : x.findvspaging(1, 5))
//	{
//		System.out.println(j.getId());
//		System.out.println(j.getQuestion());
//		System.out.println(j.getAnswer());
//	}
}



//Phan Trang ..............................................................


		public List<Faqentities> findvspaging (int start, int end){
			
			String sql = "select * from (select ROW_NUMBER() over (order by  id asc) as row"
					+", * from FAQ) AS LogWithRowNumbers "
					+"where row>="+start+" and row<="+end;
			
			Connection con = this.condb.getConnectDB();
			List<Faqentities> listld = new ArrayList<Faqentities>();
			try {
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(sql);
				while(rs.next())
				{
					int id=rs.getInt("id");
					String ask=rs.getString("title");
					String answer=rs.getString("message");
					Faqentities ld = new Faqentities(id, ask, answer);
					listld.add(ld);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return listld;
		}
		
		// lay tong so dong ..............
		public int findtotalrows()
		{
			int count=0;
			String sql="SELECT COUNT(id) as Totalrow FROM faq";
			Connection con = this.condb.getConnectDB();
			try {
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(sql);
				if(rs.next())
				{
					count =Integer.parseInt(rs.getString(1));
					//count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			return count;
		}

}
